﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Entity;

namespace DAL
{
    public class EscolaWrapper : BaseWrapper, IWrapper<Escola>
    {
        public List<Escola> GetAll()
        {
            List<Escola> LISTA = new List<Escola>();

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESCOLA_GETALL", cn);
            cmd.CommandType = CommandType.StoredProcedure;


            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Escola Esc = new Escola();

                    Esc.IDESCOLA = int.Parse(dr["IDESCOLA"].ToString());
                    Esc.IDUSUARIO = int.Parse(dr["IDUSUARIO"].ToString());
                    Esc.NOME = dr["NOME"].ToString();
                    Esc.CNPJ = dr["CNPJ"].ToString();
                    Esc.DATACRIACAO = DateTime.Parse(dr["DATACRIACAO"].ToString());
                    Esc.DATACADASTRO = DateTime.Parse(dr["DATACADASTRO"].ToString());
                    Esc.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());

                    LISTA.Add(Esc);

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Leitura de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return LISTA;
        }

        public Escola GetById(int id)
        {
            Escola Esc = new Escola();

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESCOLA_GETBYID", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDESCOLA", id);

            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    Esc.IDESCOLA = int.Parse(dr["IDESCOLA"].ToString());
                    Esc.IDUSUARIO = int.Parse(dr["IDUSUARIO"].ToString());
                    Esc.NOME = dr["NOME"].ToString();
                    Esc.CNPJ = dr["CNPJ"].ToString();
                    Esc.DATACRIACAO = DateTime.Parse(dr["DATACRIACAO"].ToString());
                    Esc.DATACADASTRO = DateTime.Parse(dr["DATACADASTRO"].ToString());
                    Esc.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Leitura de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return Esc;
        }

        public bool Insert(Escola entity)
        {
            bool status = false;
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESCOLA_INSERT", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parametro = new SqlParameter("@IDESCOLA", DbType.UInt32);
            parametro.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(parametro);


            cmd.Parameters.AddWithValue("@IDUSUARIO", entity.IDUSUARIO);
            cmd.Parameters.AddWithValue("@NOME", entity.NOME);
            cmd.Parameters.AddWithValue("@CNPJ", entity.CNPJ);
            cmd.Parameters.AddWithValue("@DATACRIACAO", entity.DATACRIACAO);
            cmd.Parameters.AddWithValue("@DATACADASTRO", entity.DATACADASTRO);
            cmd.Parameters.AddWithValue("@IDENDERECO", entity.IDENDERECO);
            cmd.Parameters.AddWithValue("@TIPOESCOLA", entity.TIPOESCOLA);


            try
            {
                cn.Open();
                if (cmd.ExecuteNonQuery() > 0)
                    status = true;
                entity.IDESCOLA = int.Parse(cmd.Parameters[0].Value.ToString());
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;
        }

        public bool Update(Escola entity)
        {
            bool status = false;
            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESCOLA_UPDATE", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDESCOLA", entity.IDESCOLA);
            cmd.Parameters.AddWithValue("@IDUSUARIO", entity.IDUSUARIO);
            cmd.Parameters.AddWithValue("@NOME", entity.NOME);
            cmd.Parameters.AddWithValue("@CNPJ", entity.CNPJ);
            cmd.Parameters.AddWithValue("@DATACRIACAO", entity.DATACRIACAO);
            cmd.Parameters.AddWithValue("@DATACADASTRO", entity.DATACADASTRO);
            cmd.Parameters.AddWithValue("@IDENDERECO", entity.IDENDERECO);
            cmd.Parameters.AddWithValue("@TIPOESCOLA", entity.TIPOESCOLA);


            try
            {
                cn.Open();
                if (cmd.ExecuteNonQuery() > 0)
                    status = true;

            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;
        }

        public bool Delete(Escola entity)
        {
            throw new NotImplementedException();
        }


        public Escola GetByIdUsuario(int id)
        {
            Escola Esc = new Escola();

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESCOLA_GETBYIDUSUARIO", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDUSUARIO", id);

            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    Esc.IDESCOLA = int.Parse(dr["IDESCOLA"].ToString());
                    Esc.IDUSUARIO = int.Parse(dr["IDUSUARIO"].ToString());
                    Esc.NOME = dr["NOME"].ToString();
                    Esc.CNPJ = dr["CNPJ"].ToString();
                    Esc.DATACRIACAO = DateTime.Parse(dr["DATACRIACAO"].ToString());
                    Esc.DATACADASTRO = DateTime.Parse(dr["DATACADASTRO"].ToString());
                    Esc.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
            }
            finally
            {
                ;
                cn.Close();
            }
            return Esc;
        }
    }
}
